using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;
using System.Data;
using System.Collections;

namespace DAOLayer
{
    public class DocGiaDao
    {
        public void ThemDocGia(DocGiaDto dg)
        {
            OleDbConnection cn = Database.ConnectionData();
            string strSQL = "Insert into DocGia (MaDocGia,HoTenDocGia,NgaySinh,DiaChi,DienThoai,Email,NgayLapThe,NguoiLapThe,TinhTrang) values(?,?,?,?,?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            
            cmd.Parameters.Add("@MaDocGia", OleDbType.WChar);
            cmd.Parameters["@MaDocGia"].Value = dg.MaDocGia;

            cmd.Parameters.Add("@HoTenDocGia", OleDbType.WChar);
            cmd.Parameters["@HoTenDocGia"].Value = dg.HoTenDocGia;

            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters["@NgaySinh"].Value = dg.NgaySinh;

            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters["@DiaChi"].Value = dg.DiaChi;

            cmd.Parameters.Add("@DienThoai", OleDbType.WChar);
            cmd.Parameters["@DienThoai"].Value = dg.DienThoai;

            cmd.Parameters.Add("@Email", OleDbType.WChar);
            cmd.Parameters["@Email"].Value = dg.Email;

            cmd.Parameters.Add("@NgayLapThe", OleDbType.Date);
            cmd.Parameters["@NgayLapThe"].Value = dg.NgayLapThe;


            cmd.Parameters.Add("@NguoiLapThe", OleDbType.WChar);
            cmd.Parameters["@NguoiLapThe"].Value = dg.NguoiLapThe;

            cmd.Parameters.Add("@TinhTrang", OleDbType.Integer);
            cmd.Parameters["@TinhTrang"].Value = dg.TinhTrangThe;

            cmd.ExecuteNonQuery();
            cn.Close();
        }


        public void CapNhatBangDocGia(Database dt)
        {
            OleDbConnection cn = Database.ConnectionData();
            String strSQL = "Select * From DocGia";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            //da.Update(dt);
            cn.Close();

        }

        public void CapNhatDocGia(DocGiaDto dg)
        {

            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Update DocGia Set HoTenDocGia=? and  NgaySinh = ? and DiaChi = ? and DienThoai = ? and Email = ? NgayLapThe = ? and NguoiLapThe = ? and TinhTrang = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@HoTenDocGia", OleDbType.WChar);
            cmd.Parameters["@HoTenDocGia"].Value = dg.HoTenDocGia;

            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters["@NgaySinh"].Value = dg.NgaySinh;

            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters["@DiaChi"].Value = dg.DiaChi;

            cmd.Parameters.Add("@DienThoai", OleDbType.WChar);
            cmd.Parameters["@DienThoai"].Value = dg.DienThoai;

            cmd.Parameters.Add("@Email", OleDbType.WChar);
            cmd.Parameters["@Email"].Value = dg.Email;

            cmd.Parameters.Add("@NgayLapThe", OleDbType.Date);
            cmd.Parameters["@NgayLapThe"].Value = dg.NgayLapThe;


            cmd.Parameters.Add("@NguoiLapThe", OleDbType.WChar);
            cmd.Parameters["@NguoiLapThe"].Value = dg.NguoiLapThe;

            cmd.Parameters.Add("@TinhTrangThe", OleDbType.Integer);
            cmd.Parameters["@TinhTrangThe"].Value = dg.TinhTrangThe;

            cmd.ExecuteNonQuery();
            cn.Close();

        }

        public DocGiaDto TimKiem(int ma)
        {
            DocGiaDto dgDto = null;
            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From DocGia Where MaDocGia = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaDocGia", OleDbType.WChar);
            cmd.Parameters["@MaDocGia"].Value = ma;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                dgDto = new DocGiaDto();
                dgDto.MaDocGia = (string)dr["MaDocGia"];
                dgDto.HoTenDocGia= (string)dr["HoTenDocGia"];
                dgDto.NgaySinh = (DateTime)dr["NgaySinh"];
                dgDto.DiaChi = (string)dr["DiaChi"];
                dgDto.DienThoai = (string)dr["DienThoai"];
                dgDto.Email = (string)dr["Email"];
                dgDto.NgayLapThe = (DateTime)dr["NgayLapThe"];               
                dgDto.NguoiLapThe = (string)dr["NguoiLapThe"];
                dgDto.TinhTrangThe = (int)dr["TinhTrangThe"];
            }

            dr.Close();
            cn.Close();
            return dgDto;
        }

        public DataTable LayBangDocGia()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL = "Select * From DocGia";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public ArrayList LayDanhSachDocGia()
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From DocGia";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DocGiaDto dgDto = new DocGiaDto();
                dgDto.MaDocGia = (string)dr["MaDocGia"];
                dgDto.HoTenDocGia = (string)dr["HoTenDocGia"];
                dgDto.NgaySinh = (DateTime)dr["NgaySinh"];
                dgDto.DiaChi = (string)dr["DiaChi"];
                dgDto.DienThoai = (string)dr["DienThoai"];
                dgDto.Email = (string)dr["Email"];
                dgDto.NgayLapThe = (DateTime)dr["NgayLapThe"];
                dgDto.NguoiLapThe = (string)dr["NguoiLapThe"];
                dgDto.TinhTrangThe = (int)dr["TinhTrangThe"];
                ds.Add(dgDto);
            }

            dr.Close();
            cn.Close();
            return ds;
        }
    }
}
